<?php

namespace App\Console\Commands;

use App\Models\Warranty;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;

class StockStat extends Command
{
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'command:StockStat {start_id} {end_id}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Execute the console command.
     *
     * @return int
     */
    public function handle()
    {
        $start_id = $this->argument('start_id');
        $end_id = $this->argument('end_id');
        $this->statOldData($start_id, $end_id);
        return 0;
    }

    public function statOldData($start_id, $end_id)
    {
        $id = $start_id;
        while (true) {
            echo $id; echo PHP_EOL;
            $rolls = DB::table('xpel_roll_stat')->where('id', '>=', $id)->where('id', '<', $end_id)->paginate(200);
            if (empty($rolls)) {
                break;
            }

            foreach ($rolls as $roll) {
                $id = $roll->id;
                $sql = 'SELECT sum(roll_sales.length) AS total_length, roll_id, roll_master.length as master_length, roll_master.product_type_id, roll_master.roll_number FROM roll_sales LEFT JOIN roll_master ON roll_sales.roll_id=roll_master.id WHERE sold_to_user_id=' . $roll->user_id . ' AND roll_sales.created_at<="' . $roll->create_date . '" GROUP BY roll_id';
                $sale_data = DB::select($sql);
                if (empty($sale_data)) {
                    continue;
                }

                $stock_1 = 0;
                $stock_2 = 0;
                $stock_3 = 0;
                foreach ($sale_data as $sale) {
                    if ($sale->master_length <= 0) {
                        continue;
                    }
                    $roll_length = $sale->total_length;
                    if (strtotime($roll->create_date) > strtotime('2020-12-15')) {
                        $sql = 'SELECT sum(length) AS transfer_len FROM roll_transfer WHERE roll_id=' . $sale->roll_id . ' AND transfer_by_user_id=' . $roll->user_id . ' AND approved=1 AND approved_date<="' . $roll->create_date . '"' ;
                        $transfer_by_data = DB::select($sql);
                        $roll_length = $roll_length - ($transfer_by_data && $transfer_by_data[0] ? $transfer_by_data[0]->transfer_len : 0);

                        $sql = 'SELECT sum(length) AS transfer_len FROM roll_transfer WHERE roll_id=' . $sale->roll_id . ' AND  transfer_to_user_id=' . $roll->user_id . ' AND approved=1 AND approved_date<="' . $roll->create_date . '"' ;
                        $transfer_to_data = DB::select($sql);
                        $roll_length = $roll_length + ($transfer_to_data && $transfer_to_data[0] ? $transfer_to_data[0]->transfer_len : 0);

                        $sql = 'SELECT sum(length) AS return_len FROM roll_return WHERE roll_id=' . $sale->roll_id . ' AND  user_id=' . $roll->user_id . ' AND status=1 AND created_at<="' . $roll->create_date . '"' ;
                        $return_data = DB::select($sql);
                        $roll_length = $roll_length - ($return_data && $return_data[0] ? $return_data[0]->roll_return : 0);
                    }

                    $sql = 'SELECT SUM(length) AS total FROM warranty_to_vehicle_coverage AS wc LEFT JOIN warranty AS w ON wc.warranty_id=w.id WHERE w.user_id=' . $roll->user_id . ' AND approved=1 AND installation_date<="' . $roll->create_date . '" AND roll_number="' . $sale->roll_number . '"' ;
                    $warranty = DB::select($sql);
                    if (!empty($warranty) && !empty($warranty[0]) && !empty($warranty[0]->total)) {
                        $roll_length = $roll_length - ($warranty && $warranty[0] && $warranty[0]->total ? $warranty[0]->total : 0);
                    }
                    $roll_num =round($roll_length / $sale->master_length, 2);
                    if ($roll_length > 0) {
                        $stock_1 += $sale->product_type_id == 1 ? $roll_num : 0;
                        $stock_2 += $sale->product_type_id == 2 ? $roll_num : 0;
                        $stock_3 += $sale->product_type_id == 3 ? $roll_num : 0;
                    }
                }

                if ($stock_1 == 0 && $stock_2 == 0 && $stock_3 == 0) {
                    continue;
                }

                $updateData = [
                    'stock_1'   => $stock_1,
                    'stock_2'   => $stock_2,
                    'stock_3'   => $stock_3,
                ];
                DB::table('xpel_roll_stat')->where('id', $id)->update($updateData);
            }

        }
    }
}
